package com.scalar.chiptrack.operations.process.dao;

//import java.sql.Statement;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.HashMap;
import java.util.Iterator;

import org.apache.struts.upload.FormFile;
import org.apache.struts.util.LabelValueBean;

import com.scalar.ScalarException;
import com.scalar.chiptrack.db.DBTransaction;
import com.scalar.chiptrack.db.DataSource;
import com.scalar.chiptrack.operations.process.BoardMaking;
import com.scalar.chiptrack.operations.process.BomRevOrSchmFileDetails;
import com.scalar.chiptrack.operations.process.BomRevOrSchmFileList;
import com.scalar.chiptrack.operations.process.BomRevSchemaFile;
import com.scalar.chiptrack.operations.process.KitMaking;
import com.scalar.chiptrack.operations.process.ProcessStep;
import com.scalar.chiptrack.operations.process.Upload;
import com.scalar.chiptrack.operations.process.forms.BoardKitForm;
import com.scalar.chiptrack.operations.vendor.dao.VendorDAO;
import com.scalar.chiptrack.utils.SqlQueryMappings;
import com.scalar.chiptrack.utils.StringUtils;

//import org.apache.struts.upload.FormFile;


public class KitMakingDAO
{


    public static void saveKit ( DBTransaction dbTransaction , int productId , Iterator kitMakingList ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        boolean status = false;

        if ( kitMakingList == null )
        {
            return;
        }

        try
        {
            // Get the connection from regular datasource if its not in a manual transaction
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection ();
            }
            else
            {
                conn = dbTransaction.getConnection ();
            }

            pstmt = conn.prepareStatement ( SqlQueryMappings.get ( "operations.process.kit.insertKit" ) );

            KitMaking kitMaking = null;

            while ( kitMakingList.hasNext () )
            {
                kitMaking = ( KitMaking ) kitMakingList.next ();
                pstmt.setInt ( 1 , productId );
                pstmt.setInt ( 2 , kitMaking.getProcessStepId () );
                pstmt.setFloat ( 3 , kitMaking.getPercent () );
                pstmt.addBatch ();
            }
            pstmt.executeBatch ();
            status = true;
        }
        catch ( Exception sql )
        {
            sql.printStackTrace ();
            throw new ScalarException ( "Could not save kit amking list" , sql );
        }
        finally
        {

            // Commit/Rollback here it selef -- if its not in a manual transaction
            if ( dbTransaction == null )
            {
                try
                {
                    if ( conn != null )
                    {
                        if ( status == true )
                        {
                            conn.commit ();
                        }
                        else
                        {
                            conn.rollback ();
                        }
                    }
                }
                catch ( SQLException ignore )
                {
                }
            }

            if ( pstmt != null )
            {
                try
                {
                    pstmt.close ();
                }
                catch ( SQLException se )
                {
                }
            }
            if ( conn != null )
            {
                try
                {
                    conn.close ();
                }
                catch ( SQLException se )
                {
                }
            }
        }

    }


    public static int saveProductKit ( DBTransaction dbTransaction , String kitName ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        boolean status = false;
        int productId = -1;


        try
        {
// Get the connection from regular datasource if its not in a manual transaction
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection ();
            }
            else
            {
                conn = dbTransaction.getConnection ();
            }

            pstmt = conn.prepareStatement ( SqlQueryMappings.get ( "operations.process.kit.insertProductKit" ) );
            pstmt.setString ( 1 , kitName );
            pstmt.setString ( 2 , kitName );
            pstmt.execute ();
            pstmt.close ();
            pstmt = null;

            pstmt = conn.prepareStatement ( "SELECT @@IDENTITY as product_id" );
            rset = pstmt.executeQuery ();

            if ( rset.next () != false )
            {
                productId = rset.getInt ( "product_id" );
            }

            rset.close ();
            rset = null;
            status = true;
        }
        catch ( Exception sql )
        {
            sql.printStackTrace ();
            throw new ScalarException ( "Could not save Kit in product table" , sql );
        }
        finally
        {

// Commit/Rollback here it selef -- if its not in a manual transaction
            if ( dbTransaction == null )
            {
                try
                {
                    if ( conn != null )
                    {
                        if ( status == true )
                        {
                            conn.commit ();
                        }
                        else
                        {
                            conn.rollback ();
                        }
                    }
                }
                catch ( SQLException ignore )
                {
                }
            }

            if ( pstmt != null )
            {
                try
                {
                    pstmt.close ();
                }
                catch ( SQLException se )
                {
                }
            }
            if ( conn != null )
            {
                try
                {
                    conn.close ();
                }
                catch ( SQLException se )
                {
                }
            }
        }


        return productId;

    }


    public static ArrayList loadKitMaking () throws ScalarException
    {

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;

        KitMaking kitMaking = null;
        ArrayList kitMakingList = new ArrayList ();

        try
        {
            conn = DataSource.getConnection ();

            pstmt = conn.prepareStatement ( SqlQueryMappings.get ( "operations.kit.loadKitMaming" ) );
            rset = pstmt.executeQuery ();

            while ( rset.next () )
            {
                kitMaking = new KitMaking ();
                kitMaking.setProductId ( rset.getInt ( "product_id" ) );
                kitMaking.setMarketingPartNo ( rset.getString ( "Part_Number" ) );
                kitMakingList.add ( kitMaking );

            }
        }
        catch ( Exception sqle )
        {
            sqle.printStackTrace ();
            throw new ScalarException ( "Could not load finished goods data from database" , sqle );
        }
        finally
        {
            if ( rset != null )
            {
                try
                {
                    rset.close ();
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( pstmt != null )
            {
                try
                {
                    pstmt.close ();
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( conn != null )
            {
                try
                {
                    conn.close ();
                }
                catch ( SQLException ignore )
                {
                }
            }
        }

        return kitMakingList;
    }


    public static ArrayList loadKit ( String productId ) throws ScalarException
    {

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;

        KitMaking kitMaking = null;
        ArrayList kitMakingList = new ArrayList ();

        try
        {
            conn = DataSource.getConnection ();

            pstmt = conn.prepareStatement ( SqlQueryMappings.get ( "operations.kit.loadExistingKit" ) );
            pstmt.setString ( 1 , productId );
            rset = pstmt.executeQuery ();

            while ( rset.next () )
            {
                kitMaking = new KitMaking ();
                kitMaking.setMarketingPartNo ( rset.getString ( "P_Marketing_Part_Number" ) );
                kitMaking.setPercent ( rset.getInt ( "Percentage" ) );
                kitMakingList.add ( kitMaking );

            }
        }
        catch ( Exception sqle )
        {
            sqle.printStackTrace ();
            throw new ScalarException ( "Could not load existing kit from the database" , sqle );
        }
        finally
        {
            if ( rset != null )
            {
                try
                {
                    rset.close ();
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( pstmt != null )
            {
                try
                {
                    pstmt.close ();
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( conn != null )
            {
                try
                {
                    conn.close ();
                }
                catch ( SQLException ignore )
                {
                }
            }
        }

        return kitMakingList;
    }


    //For loading all the boards in the select box .. so user can select the board and modify the details
    public static HashMap loadAllBoards () throws ScalarException
    {

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        HashMap listMap = new HashMap ();


        try
        {
            conn = DataSource.getConnection ();

            pstmt = conn.prepareStatement ( SqlQueryMappings.get ( "operations.boards.loadAllBoards" ) );
            rset = pstmt.executeQuery ();

            while ( rset.next () )
            {
                listMap.put ( rset.getInt ( "Product_ID" ) + "" , rset.getString ( "Part_Number" ) );
            }
        }
        catch ( Exception sqle )
        {
            sqle.printStackTrace ();
            throw new ScalarException ( "Could not load boards data from database" , sqle );
        }
        finally
        {
            if ( rset != null )
            {
                try
                {
                    rset.close ();
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( pstmt != null )
            {
                try
                {
                    pstmt.close ();
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( conn != null )
            {
                try
                {
                    conn.close ();
                }
                catch ( SQLException ignore )
                {
                }
            }
        }

        return listMap;
    }

    /*
     *Bhaskar added this method inorder to place/segregate the boards and kits
     *from one another on 28 july 2003.
     */
    public static ArrayList loadAllBoardsAndKits (String processType) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        ArrayList listMap = new ArrayList ();
        PartNumber partNumbers = null;
        try
        {
            conn = DataSource.getConnection ();
            pstmt = conn.prepareStatement ( SqlQueryMappings.get ( "operations.boards.loadAllBoards" ) );
            pstmt.setString(1, processType);
            rset = pstmt.executeQuery ();

            while ( rset.next () )
            {
                partNumbers = new PartNumber ();
                partNumbers.setPartNumber ( rset.getString ( "Part_Number" ) );
                partNumbers.setProductId ( rset.getInt ( "Product_ID" ) + "" );
                listMap.add ( new LabelValueBean (
                        partNumbers.getPartNumber () , partNumbers.getProductId () ) );
            }
        }
        catch ( Exception sqle )
        {
            sqle.printStackTrace ();
            throw new ScalarException ( "Could not load boards data from database" , sqle );
        }
        finally
        {
            if ( rset != null )
            {
                try
                {
                    rset.close ();
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( pstmt != null )
            {
                try
                {
                    pstmt.close ();
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( conn != null )
            {
                try
                {
                    conn.close ();
                }
                catch ( SQLException ignore )
                {
                }
            }
        }
        return listMap;
    }

    //Load the Board Details
    public static BoardKitForm loadBoardDetails ( String productId ) throws ScalarException
    {

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        BoardKitForm board = new BoardKitForm ();
        //system.out.println("Product Id is >> " + productId);

        if ( ( productId == null ) || ( productId.equals ( "null" ) ) )
        {
            return null;
        }

        if ( ( productId != null ) && ( productId.equals ( "-2" ) ) )
        {
            board.setVendorLocations ( VendorDAO.getFinishedGoodsLocations () );
            return board;
        }

        try
        {
            conn = DataSource.getConnection ();
            pstmt = conn.prepareStatement ( SqlQueryMappings.get ( "operations.boards.loadBoardDetails" ) );
            pstmt.setInt ( 1 , Integer.parseInt ( productId ) );
            rset = pstmt.executeQuery ();

            if ( rset.next () )
            {
                board = new BoardKitForm ();
                board.setProductId ( Integer.parseInt ( productId ) );
                board.setPartNumber ( rset.getString ( "Part_Number" ) );
                board.setProductType ( rset.getString ( "P_Type" ) );
                board.setProcessStepId ( rset.getInt ( "ProcessStep_ID" ) );
                board.setDescription ( rset.getString ( "PS_Comments" ) );
                String unitPrice = "";
                try
                {
                    unitPrice = rset.getString ( "Unit_Price" );
                    if ( ( unitPrice == null ) || ( ( unitPrice != null ) && ( unitPrice.equalsIgnoreCase ( "null" ) ) ) )
                    {
                        unitPrice = "0.00";
                    }
                    else
                    {
                        // modified by Murali krishna
                        //unitPrice = StringUtils.formatAmount ( ( float ) ( new Double ( unitPrice ).doubleValue () ) , "###.##" );
                        unitPrice = StringUtils.formatAmount ( Double.parseDouble(unitPrice) , "###.##" );
                    }
                }
                catch ( Exception e )
                {
                    unitPrice = "0.00";
                }
                board.setUnitPrice ( unitPrice );
                boolean bools = rset.getBoolean ( "Is_Approved" );
                //This value bools is passed as a compliment because in our system 1 = false and 0 = true;
                board.setIsApproved ( !bools );
                board.setVendorLocations ( VendorDAO.getFinishedGoodsLocations ( board.getProcessStepId () ) );
            }
        }
        catch ( Exception sqle )
        {
            sqle.printStackTrace ();
            throw new ScalarException ( "Could not load boards data from database" , sqle );
        }
        finally
        {
            if ( rset != null )
            {
                try
                {
                    rset.close ();
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( pstmt != null )
            {
                try
                {
                    pstmt.close ();
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( conn != null )
            {
                try
                {
                    conn.close ();
                }
                catch ( SQLException ignore )
                {
                }
            }
        }

        return board;
    }


    public static void saveBoardPrice ( DBTransaction dbTransaction , int productId , String price , boolean insert ) throws ScalarException
    {

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        boolean status = false;

        // modified by Murali krishna
        // float unitPrice = 0.0f;
        double unitPrice = 0.0;


        try
        {
            // Get the connection from regular datasource if its not in a manual transaction
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection ();
            }
            else
            {
                conn = dbTransaction.getConnection ();
            }

            if ( price != null )
            {
                // Modified By Murali Krishna
                //unitPrice = Float.parseFloat ( price );
                unitPrice = Double.parseDouble(price);
            }

            if ( insert )
            {
                if ( productId != -1 )
                {
                    pstmt = conn.prepareStatement ( SqlQueryMappings.get ( "operations.process.board.insertBoardPrice" ) );
                    pstmt.setInt ( 1 , productId );
                    // modified by Murali krishna
                    //pstmt.setFloat ( 2 , unitPrice );
                    pstmt.setDouble ( 2 , unitPrice );
                    pstmt.execute ();
                    pstmt.close ();
                }
            }
            else
            {
                pstmt = conn.prepareStatement ( SqlQueryMappings.get ( "operations.process.board.updateBoardPrice" ) );
                // modified by Murali krishna
                //pstmt.setFloat ( 1 , unitPrice );
                pstmt.setDouble ( 1 , unitPrice );
                pstmt.setInt ( 2 , productId );
                pstmt.execute ();
                pstmt.close ();
            }

            status = true;
        }
        catch ( Exception sql )
        {
            sql.printStackTrace ();
            throw new ScalarException ( "Could not save Kit in product table" , sql );
        }
        finally
        {
            // Commit/Rollback here it selef -- if its not in a manual transaction
            if ( dbTransaction == null )
            {
                try
                {
                    if ( conn != null )
                    {
                        if ( status == true )
                        {
                            conn.commit ();
                        }
                        else
                        {
                            conn.rollback ();
                        }
                    }
                }
                catch ( SQLException ignore )
                {
                }
            }

            if ( pstmt != null )
            {
                try
                {
                    pstmt.close ();
                }
                catch ( SQLException se )
                {
                }
            }
            if ( conn != null )
            {
                try
                {
                    conn.close ();
                }
                catch ( SQLException se )
                {
                }
            }
        }

    }
    //This method signature is changed by one more variable "int isApproved" (Bhaskar on 17 Sep 2003
    /*public static void saveProductDetails(DBTransaction dbTransaction,int productId,
    String type, String description, int isApproved ) throws ScalarException
    {

        Connection conn = null;
	PreparedStatement pstmt = null;
	ResultSet rset = null;
	boolean status = false;


        try
        {
             // Get the connection from regular datasource if its not in a manual transaction
             if (dbTransaction == null) {
                conn = DataSource.getConnection();
             }
             else {
                conn = dbTransaction.getConnection();
             }


            pstmt = conn.prepareStatement(SqlQueryMappings.get("operations.process.board.updateProductDetails"));
            //System.out.println ("********** type = " + type);
            pstmt.setString(1, type);
            pstmt.setString(2, description);
            pstmt.setInt(3, isApproved);
            pstmt.setInt(4, productId);
            pstmt.execute();

             status = true;
	    }
        catch(Exception sql)
        {
             sql.printStackTrace();
             throw new ScalarException("Could not save Kit in product table", sql);
	     }
        finally
        {
             // Commit/Rollback here it selef -- if its not in a manual transaction
             if (dbTransaction == null) {
                  try {
                     if (conn != null) {
                          if (status == true) {
                                conn.commit();
                          }
                          else {
                                conn.rollback();
                          }
                     }
                 }
                 catch (SQLException ignore) {}
             }

             if(pstmt != null){
                      try{pstmt.close();}catch(SQLException se){}
             }
             if(conn != null){
                      try{ conn.close(); }catch(SQLException se){}
             }
	   }

    }*/

    // Balaram, on June 30, 2003
    // changed by hareesh on 15th July 2003  // to make Partnumber  and Manufacturing same
    //This method signature is changed by one more variable "int isApproved" (Bhaskar on 17 Sep 2003)
    public static void updateProductTypeAndDescription ( DBTransaction dbTransaction , int productId , String productType , String description , String partNumber , String mktPartNumber , int isApproved ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        boolean status = false;


        try
        {
            // Get the connection from regular datasource if its not in a manual transaction
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection ();
            }
            else
            {
                conn = dbTransaction.getConnection ();
            }

            pstmt = conn.prepareStatement ( SqlQueryMappings.get ( "operations.process.board.updateProductTypeAndDescription" ) );

            pstmt.setString ( 1 , productType );
            pstmt.setString ( 2 , description );
            pstmt.setString ( 3 , partNumber );
            pstmt.setString ( 4 , mktPartNumber );
            pstmt.setInt ( 5 , isApproved );
            pstmt.setInt ( 6 , productId );
            pstmt.execute ();

            status = true;
        }
        catch ( Exception sql )
        {
            sql.printStackTrace ();
            throw new ScalarException ( "Could not update product type in product table" , sql );
        }
        finally
        {
            // Commit/Rollback here it selef -- if its not in a manual transaction
            if ( dbTransaction == null )
            {
                try
                {
                    if ( conn != null )
                    {
                        if ( status == true )
                        {
                            conn.commit ();
                        }
                        else
                        {
                            conn.rollback ();
                        }
                    }
                }
                catch ( SQLException ignore )
                {
                }
            }

            if ( pstmt != null )
            {
                try
                {
                    pstmt.close ();
                }
                catch ( SQLException se )
                {
                }
            }
            if ( conn != null )
            {
                try
                {
                    conn.close ();
                }
                catch ( SQLException se )
                {
                }
            }
        }

    }


    public static void saveBoardinInventory ( DBTransaction dbTransaction , String partNumber , int processStepId , boolean insert ) throws ScalarException
    {

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        float unitPrice = 0.0f;
        boolean status = false;

        try
        {
            // Get the connection from regular datasource if its not in a manual transaction
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection ();
            }
            else
            {
                conn = dbTransaction.getConnection ();
            }

            if ( insert )
                pstmt = conn.prepareStatement ( SqlQueryMappings.get ( "operations.process.board.insertBoardInventory" ) );
            else
                pstmt = conn.prepareStatement ( SqlQueryMappings.get ( "operations.process.board.updateBoardInventory" ) );

            pstmt.setString ( 1 , partNumber );
            pstmt.setString ( 2 , partNumber );
            pstmt.setInt ( 3 , processStepId );
            pstmt.execute ();
            status = true;
        }
        catch ( Exception sql )
        {
            sql.printStackTrace ();
            throw new ScalarException ( "Could not save Board in product table" , sql );
        }
        finally
        {
            // Commit/Rollback here it selef -- if its not in a manual transaction
            if ( dbTransaction == null )
            {
                try
                {
                    if ( conn != null )
                    {
                        if ( status == true )
                        {
                            conn.commit ();
                        }
                        else
                        {
                            conn.rollback ();
                        }
                    }
                }
                catch ( SQLException ignore )
                {
                }
            }

            if ( pstmt != null )
            {
                try
                {
                    pstmt.close ();
                }
                catch ( SQLException se )
                {
                }
            }
            if ( conn != null )
            {
                try
                {
                    conn.close ();
                }
                catch ( SQLException se )
                {
                }
            }
        }

    }


    /*
    This method is added by Bhaskar on 16 Feb 2004. This retrieves the part number and its product id
    of all child boards/kits for a given parent product board/kit. This is being written as a new method
    because in future the client requirements may even grow and each child board may have separate set of
    price, description and other specifications.
    */

    public static ArrayList getChildBoardOrKitDetails ( int productId ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        ArrayList childBoards = new ArrayList ();
        BoardMaking board = new BoardMaking ();

        try
        {
            conn = DataSource.getConnection ();
            pstmt = conn.prepareStatement ( SqlQueryMappings.get ( "operations.process.board.getChildBoardOrKitDetails" ) );
            pstmt.setInt ( 1 , productId );
            rset = pstmt.executeQuery ();
            while ( rset.next () )
            {
                board.setChildProductId ( rset.getInt ( "product_id" ) + "" );
                board.setChildBoardName ( rset.getString ( "Part_Number" ) );
                board.setOriginalChildBoardName ( rset.getString ( "Part_Number" ) );
                childBoards.add ( board );
                board = new BoardMaking ();
            }
        }
        catch ( Exception sqle )
        {
            sqle.printStackTrace ();
            throw new ScalarException ( "Could not load child boards or kits data from database" , sqle );
        }
        finally
        {
            if ( rset != null )
            {
                try
                {
                    rset.close ();
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( pstmt != null )
            {
                try
                {
                    pstmt.close ();
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( conn != null )
            {
                try
                {
                    conn.close ();
                }
                catch ( SQLException ignore )
                {
                }
            }
        }
        return childBoards;
    }


    public static boolean saveChildBoardDetails ( DBTransaction dbTransaction , ArrayList childBoards , String description ,
                                                  String productType , int baseProductId , int isApproved , String STEP_TYPE ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean status = false;
        int parentProductId = baseProductId;

        if ( ( childBoards == null ) || ( childBoards.size () == 0 ) )
        {
            return status;
        }

        try
        {
            int childBoardsSize = childBoards.size ();

            for ( int i = 0 ; i < childBoardsSize ; i++ )
            {
                int processStepId = ProcessStep.getProcessStepID ( dbTransaction , STEP_TYPE , STEP_TYPE , description );
                String tempPartNum = ( ( BoardMaking ) childBoards.get ( i ) ).getChildBoardName ();
                saveChildBoardRecords ( dbTransaction , processStepId , tempPartNum , description , productType , baseProductId , isApproved );
            }

            status = true;
        }
        catch ( Exception sql )
        {
            sql.printStackTrace ();
            throw new ScalarException ( "Could not save Child Boards" , sql );
        }

        finally
        {
            // Commit/Rollback here it self -- if its not in a manual transaction
            if ( dbTransaction == null )
            {
                try
                {
                    if ( conn != null )
                    {
                        if ( status == true )
                        {
                            conn.commit ();
                        }
                        else
                        {
                            conn.rollback ();
                        }
                    }
                }
                catch ( SQLException ignore )
                {
                }
            }

            if ( pstmt != null )
            {
                try
                {
                    pstmt.close ();
                }
                catch ( SQLException se )
                {
                }
            }
            if ( conn != null )
            {
                try
                {
                    conn.close ();
                }
                catch ( SQLException se )
                {
                }
            }
        }
        return status;
    }


    private static void saveChildBoardRecords ( DBTransaction dbTransaction , int processStepId , String partNumber , String description ,
                                                String productType , int baseProductId , int isApproved ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean status = false;
        int parentProductId = baseProductId;

        try
        {
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection ();
            }
            else
            {
                conn = dbTransaction.getConnection ();
            }
            pstmt = conn.prepareStatement ( SqlQueryMappings.get ( "operations.process.product.insertChildBoardDetails" ) );
            pstmt.setString ( 1 , partNumber );
            pstmt.setInt ( 2 , processStepId );
            pstmt.setInt ( 3 , parentProductId );
            pstmt.setString ( 4 , partNumber );
            pstmt.setString ( 5 , description );
            pstmt.setString ( 6 , productType );
            pstmt.setInt ( 7 , isApproved );
            pstmt.executeUpdate ();
            status = true;
        }
        catch ( Exception sqle )
        {
            sqle.printStackTrace ();
            throw new ScalarException ( "Could not save Child Boards" , sqle );
        }
        finally
        {
            // Commit/Rollback here it selef -- if its not in a manual transaction
            if ( dbTransaction == null )
            {
                try
                {
                    if ( conn != null )
                    {
                        if ( status == true )
                        {
                            conn.commit ();
                        }
                        else
                        {
                            conn.rollback ();
                        }
                    }
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( pstmt != null )
            {
                try
                {
                    pstmt.close ();
                }
                catch ( Exception e )
                {
                }
            }
            if ( conn != null )
            {
                try
                {
                    conn.close ();
                }
                catch ( Exception e )
                {
                }
            }
        }
    }


    private static void updateChildBoardRecords ( DBTransaction dbTransaction , String partNumber , String description ,
                                                  String productType , String childProductId , int isApproved ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean status = false;
        int productId = Integer.parseInt ( childProductId );

        try
        {
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection ();
            }
            else
            {
                conn = dbTransaction.getConnection ();
            }
            pstmt = conn.prepareStatement ( SqlQueryMappings.get ( "operations.process.product.updateChildBoardDetails" ) );
            pstmt.setString ( 1 , partNumber );
            pstmt.setString ( 2 , partNumber );
            pstmt.setString ( 3 , description );
            pstmt.setString ( 4 , productType );
            pstmt.setInt ( 5 , isApproved );
            pstmt.setInt ( 6 , productId );
            pstmt.executeUpdate ();
            status = true;
        }
        catch ( Exception sqle )
        {
            sqle.printStackTrace ();
            throw new ScalarException ( "Could not save Child Boards" , sqle );
        }
        finally
        {
            // Commit/Rollback here it selef -- if its not in a manual transaction
            if ( dbTransaction == null )
            {
                try
                {
                    if ( conn != null )
                    {
                        if ( status == true )
                        {
                            conn.commit ();
                        }
                        else
                        {
                            conn.rollback ();
                        }
                    }
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( pstmt != null )
            {
                try
                {
                    pstmt.close ();
                }
                catch ( Exception e )
                {
                }
            }
            if ( conn != null )
            {
                try
                {
                    conn.close ();
                }
                catch ( Exception e )
                {
                }
            }
        }
    }


    public static boolean updateChildBoardDetails ( DBTransaction dbTransaction , ArrayList childBoards , String description ,
                                                    String productType , int baseProductId , int isApproved , String STEP_TYPE ) throws ScalarException
    {
        Connection conn = null;
        //PreparedStatement pstmt2 = null;
        ResultSet rset = null;
        boolean status = false;
        int parentProductId = baseProductId;

        if ( ( childBoards == null ) || ( childBoards.size () == 0 ) )
        {
            return status;
        }
        try
        {
            /*if (dbTransaction == null)
             {
                 conn = DataSource.getConnection();
             }
             else
             {
                 conn = dbTransaction.getConnection();
             }*/
            int childBoardsSize = childBoards.size ();

            //pstmt1 = conn.prepareStatement (SqlQueryMappings.get ("operations.process.product.insertChildBoardDetails"));
            //pstmt2 = conn.prepareStatement (SqlQueryMappings.get ("operations.process.product.updateChildBoardDetails"));

            for ( int i = 0 ; i < childBoardsSize ; i++ )
            {

                String tempPartNum = ( ( BoardMaking ) childBoards.get ( i ) ).getChildBoardName ();
                String childProductId = ( ( BoardMaking ) childBoards.get ( i ) ).getChildProductId ();
                if ( childProductId.trim ().equals ( "-1" ) )
                {
                    int processStepId = ProcessStep.getProcessStepID ( dbTransaction , STEP_TYPE , STEP_TYPE , description );
                    saveChildBoardRecords ( dbTransaction , processStepId , tempPartNum , description ,
                            productType , parentProductId , isApproved );
                }
                else
                {
                    updateChildBoardRecords ( dbTransaction , tempPartNum , description ,
                            productType , childProductId , isApproved );
                }
            }
            status = true;
        }
        catch ( Exception sql )
        {
            sql.printStackTrace ();
            throw new ScalarException ( "Could not save Child Boards" , sql );
        }

        finally
        {
            // Commit/Rollback here it selef -- if its not in a manual transaction
            if ( dbTransaction == null )
            {
                try
                {
                    if ( conn != null )
                    {
                        if ( status == true )
                        {
                            conn.commit ();
                        }
                        else
                        {
                            conn.rollback ();
                        }
                    }
                }
                catch ( SQLException ignore )
                {
                }
            }

            /*if(pstmt1 != null){
               try{pstmt1.close();}catch(SQLException se){}
           }
           if(pstmt2 != null){
               try{pstmt2.close();}catch(SQLException se){}
           }*/
            if ( conn != null )
            {
                try
                {
                    conn.close ();
                }
                catch ( SQLException se )
                {
                }
            }
        }
        return status;
    }


    // modified by saravnan

    public static void insertPcbBomSchematicValues ( DBTransaction dbTransaction , BoardKitForm kitMakingForm , int productId , boolean insert ) throws ScalarException
    {

        int parentProductId = productId;
        int bomRevId = -1;
        int schemaId = -1;
        boolean status = false;

        ArrayList bomFileList = kitMakingForm.getBomRevFileList ();
        ArrayList schemFileList = kitMakingForm.getSchematicFileList ();
        Calendar cal = Calendar.getInstance ();
        String timeString = cal.get ( Calendar.MONTH ) + "-" + cal.get ( Calendar.DATE ) + "-" + cal.get ( Calendar.YEAR ) + " ( " + cal.get ( Calendar.HOUR_OF_DAY ) + "-" + cal.get ( Calendar.MINUTE ) + " )";

        for ( int i = 0 ; i < bomFileList.size () ; i++ )
        {
            BomRevSchemaFile bomRevSchemaFile = ( BomRevSchemaFile ) ( bomFileList.get ( i ) );
            FormFile bomRevFile = bomRevSchemaFile.getFormFile ();
            String bomRevNumber = bomRevSchemaFile.getBomOrSchemaNumber ();
            String filename = timeString + bomRevFile.getFileName ().trim ();
            bomRevFile.setFileName ( filename );
            bomFileList.set ( i , bomRevFile );
            int tempBomRevId = Upload.getBomRevNoId ( dbTransaction , kitMakingForm.getProductType () , bomRevSchemaFile.getBomOrSchemaNumber () , Integer.toString ( bomRevId ) , bomRevFile );
            insertValues ( dbTransaction , tempBomRevId , -1 , kitMakingForm , parentProductId , insert , bomRevNumber );
        }

        for ( int i = 0 ; i < schemFileList.size () ; i++ )
        {
            BomRevSchemaFile bomRevSchemaFile = ( BomRevSchemaFile ) ( schemFileList.get ( i ) );
            FormFile schemaFile = bomRevSchemaFile.getFormFile ();
            String schemaNumber = bomRevSchemaFile.getBomOrSchemaNumber ();
            String filename = timeString + schemaFile.getFileName ().trim ();
            schemaFile.setFileName ( filename );
            schemFileList.set ( i , schemaFile );
            int tempSchemaId = Upload.getSchematicId ( dbTransaction , kitMakingForm.getProductType () , schemaNumber , Integer.toString ( bomRevId ) , schemaFile );
            insertValues ( dbTransaction , -1 , tempSchemaId , kitMakingForm , parentProductId , insert , schemaNumber );
        }

        if ( kitMakingForm.getProductId () != -1 )
        {
            // updates PCBRevNumer
            updatePCBRevNumber ( dbTransaction , kitMakingForm );

            // modification
            modifyBomAndSchmaFiles ( dbTransaction , kitMakingForm );

            //deletion
            deleteBomAndSchmaFiles ( dbTransaction , kitMakingForm , productId );

        }

    }

    public static void insertValues ( DBTransaction dbTransaction , int bomRevId , int schemaId , BoardKitForm kitMakingForm , int parentProductId , boolean insert , String bomRevORSchemaNum ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean status = false;
        try
        {
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection ();
            }
            else
            {
                conn = dbTransaction.getConnection ();
            }

            if ( insert )
            {

                pstmt = conn.prepareStatement ( SqlQueryMappings.get ( "operations.process.product.insertBomSchematicFileDetails" ) );
                pstmt.setInt ( 1 , parentProductId );
                pstmt.setString ( 2 , kitMakingForm.getPcbRevNo ().trim () );

                if ( bomRevId == -1 )
                {
                    pstmt.setString ( 3 , null );
                }
                else
                {
                    pstmt.setInt ( 3 , bomRevId );
                }

                if ( schemaId == -1 )
                {
                    pstmt.setString ( 4 , null );
                }
                else
                {
                    pstmt.setInt ( 4 , schemaId );
                }
                pstmt.executeUpdate ();
                status = true;
            }
        }
        catch ( Exception sql )
        {
            sql.printStackTrace ();
            throw new ScalarException ( "Could not save File  details" , sql );
        }

        finally
        {
            if ( dbTransaction == null )
            {
                try
                {
                    if ( conn != null )
                    {
                        if ( status == true )
                        {
                            conn.commit ();
                        }
                        else
                        {
                            conn.rollback ();
                        }
                    }
                }
                catch ( SQLException ignore )
                {
                }
            }

            if ( pstmt != null )
            {
                try
                {
                    pstmt.close ();
                }
                catch ( SQLException se )
                {
                   // System.out.println( "Error when closing Statement" );
                }
            }
            if ( conn != null )
            {
                try
                {
                    conn.close ();
                }
                catch ( SQLException se )
                {
                    System.out.println( "Error when closing connection" );
                }
            }
        }
    }

    public static BoardKitForm loadBOMSchematicNumbers ( BoardKitForm kitMakingForm ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        String productId = "" + kitMakingForm.getProductId ();

        if ( ( productId == null ) || ( productId.equals ( "null" ) ) )
        {
            return null;
        }

        if ( ( productId != null ) && ( productId.equals ( "-2" ) ) )
        {
            kitMakingForm.setBomRevNumber ( "" );
            kitMakingForm.setSchematicNumber ( "" );
            return kitMakingForm;
        }
        try
        {
            conn = DataSource.getConnection ();
            pstmt = conn.prepareStatement ( SqlQueryMappings.get ( "operations.boards.loadBomNumber" ) );
            pstmt.setInt ( 1 , Integer.parseInt ( productId ) );
            rset = pstmt.executeQuery ();
            if ( rset.next () )
            {
                kitMakingForm.setBomRevId ( "" + rset.getInt ( "doc_id" ) );
                kitMakingForm.setBomRevNumber ( rset.getString ( "doc_Number" ) );
            }
            rset.close ();
            pstmt.clearParameters ();
            pstmt.close ();
            pstmt = null;
            pstmt = conn.prepareStatement ( SqlQueryMappings.get ( "operations.boards.loadSchematicNumber" ) );
            pstmt.setInt ( 1 , Integer.parseInt ( productId ) );
            rset = pstmt.executeQuery ();
            if ( rset.next () )
            {
                kitMakingForm.setSchematicId ( "" + rset.getInt ( "doc_id" ) );
                kitMakingForm.setSchematicNumber ( rset.getString ( "doc_Number" ) );
            }

            if ( kitMakingForm.getBomRevId () == null )
            {
                kitMakingForm.setBomRevId ( "-1" );
            }
            if ( kitMakingForm.getSchematicId () == null )
            {
                kitMakingForm.setSchematicId ( "-1" );
            }
            pstmt.close ();
        }
        catch ( Exception sqle )
        {
            sqle.printStackTrace ();
            throw new ScalarException ( "Could not load BOM, Schematic numbers from database" , sqle );
        }
        finally
        {
            if ( rset != null )
            {
                try
                {
                    rset.close ();
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( pstmt != null )
            {
                try
                {
                    pstmt.close ();
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( conn != null )
            {
                try
                {
                    conn.close ();
                }
                catch ( SQLException ignore )
                {
                }
            }
        }
        return kitMakingForm;
    }


    public static String loadPcbRevNumber ( int productID ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        String productId = "" + productID;
        String pcbRevNo = "";

        if ( ( productId == null ) || ( productId.equals ( "null" ) ) )
        {
            return null;
        }

        if ( ( productId != null ) && ( productId.equals ( "-2" ) ) )
        {
            return "";
        }

        try
        {
            conn = DataSource.getConnection ();
            pstmt = conn.prepareStatement ( SqlQueryMappings.get ( "operations.boards.loadPcbRevNo" ) );
            pstmt.setInt ( 1 , Integer.parseInt ( productId ) );
            rset = pstmt.executeQuery ();
            if ( rset.next () )
            {
                pcbRevNo = rset.getString ( "pcb_rev_number" );
            }
        }
        catch ( Exception sqle )
        {
            sqle.printStackTrace ();
            throw new ScalarException ( "Could not load PcbRev numbers from database" , sqle );
        }
        finally
        {
            if ( rset != null )
            {
                try
                {
                    rset.close ();
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( pstmt != null )
            {
                try
                {
                    pstmt.close ();
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( conn != null )
            {
                try
                {
                    conn.close ();
                }
                catch ( SQLException ignore )
                {
                }
            }
        }
        return pcbRevNo;
    }

//--------------------------------------------------------------
    /* chakravarthy :
     * get the bom doc file names
     * and add them to an arraylis object the return the arrylist
     *
     * @param producId the product id
     * @param productType teh product type
     *
     * return  bomFileNames the arraylist coantaining the bom file names
     */
    public static ArrayList loadBomDocNames ( int productId , String productType ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement psmt = null;
        ResultSet rs = null;

        String partNumber = null;
        int bomDocId = 0;
        String bomFileLocation = null;
        ArrayList bomFileNames = new ArrayList ();

        try
        {
            conn = DataSource.getConnection ();
            psmt = conn.prepareStatement ( SqlQueryMappings.get ( "operations.boards.loadBomDocNames" ) );
            psmt.setInt ( 1 , productId );
            rs = psmt.executeQuery ();
            conn.commit ();

            while ( rs.next () )
            {
                partNumber = rs.getString ( "p_marketing_part_Number" );
                productType = rs.getString ( "p_type" );
                bomDocId = rs.getInt ( "bom_doc_id" );
                bomFileNames.add ( rs.getString ( "dt_filename" ) );
                bomFileLocation = rs.getString ( "dt_filelocation" );
            }
        }
        catch ( Exception sqle )
        {
            System.out.println ( sqle.getMessage () );
            sqle.printStackTrace ();
            throw new ScalarException ( "Could not load Bom Documents from database" , sqle );
        }
        finally
        {
            if ( rs != null )
            {
                try
                {
                    rs.close ();
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( psmt != null )
            {
                try
                {
                    psmt.close ();
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( conn != null )
            {
                try
                {
                    conn.close ();
                }
                catch ( SQLException ignore )
                {
                }
            }
        }
        return bomFileNames;
    }

    /*
    * get the schematic doc file names
    * and add them to an arraylis object the return the arrylist
    *
    * @param producId the product id
    * @param productType teh product type
    *
    * return  schematicFileNames the arraylist coantaining the schematic file names
    */
    public static ArrayList loadSchematicDocNames ( int productId , String productType ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement psmt = null;
        ResultSet rs = null;

        String partNumber = null;
        int schematicDocId = 0;
        String bomFileLocation = null;
        ArrayList schematicFileNames = new ArrayList ();

        try
        {
            conn = DataSource.getConnection ();
            psmt = conn.prepareStatement ( SqlQueryMappings.get ( "operations.boards.loadSchematicDocNames" ) );
            psmt.setInt ( 1 , productId );
            rs = psmt.executeQuery ();

            while ( rs.next () )
            {
                partNumber = rs.getString ( "p_marketing_part_Number" );
                productType = rs.getString ( "p_type" );
                schematicDocId = rs.getInt ( "schematic_doc_id" );
                schematicFileNames.add ( rs.getString ( "dt_filename" ) );
                bomFileLocation = rs.getString ( "dt_filelocation" );
            }
        }
        catch ( Exception sqle )
        {
            System.out.println ( sqle.getMessage () );
            sqle.printStackTrace ();
            throw new ScalarException ( "Could not load BomAndSchematic numbers from database" , sqle );
        }
        finally
        {
            if ( rs != null )
            {
                try
                {
                    rs.close ();
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( psmt != null )
            {
                try
                {
                    psmt.close ();
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( conn != null )
            {
                try
                {
                    conn.close ();
                }
                catch ( SQLException ignore )
                {
                }
            }
        }
        return schematicFileNames;
    }


    /**
     * modified by saravnan
     * Loads BomRev file deatils
     *
     */
    public static BomRevOrSchmFileList loadBomRevFileDetails ( int productId , String productType ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement psmt = null;
        ResultSet rs = null;

        String partNumber = null;
        int bomDocId = 0;
        String bomFileLocation = null;
        ArrayList bomFileNames = new ArrayList ();
        ArrayList fileList = new ArrayList ();
        BomRevOrSchmFileList bomRevOrSchmFileList = new BomRevOrSchmFileList ();
        try
        {
            conn = DataSource.getConnection ();
            psmt = conn.prepareStatement ( "select p.product_id, p.p_marketing_part_Number, p.p_type, bk.pcb_rev_number,bk.bom_doc_id,bk.schematic_doc_id,d.doc_id,d.doc_number,d.dt_filename,d.dt_filelocation , d.Doc_Number from product p,board_kit_table bk,doc_table d where p.product_id = bk.product_id and d.doc_id = bk.bom_doc_id and p.product_id = ?" );
            psmt.setInt ( 1 , productId );
            rs = psmt.executeQuery ();
            while ( rs.next () )
            {
                BomRevOrSchmFileDetails bomRevOrSchmFileDetails = new BomRevOrSchmFileDetails ();
                partNumber = rs.getString ( "p_marketing_part_Number" );
                productType = rs.getString ( "p_type" );
                bomDocId = rs.getInt ( "bom_doc_id" );
                bomFileNames.add ( rs.getString ( "dt_filename" ) );
                bomFileLocation = rs.getString ( "dt_filelocation" );
                bomRevOrSchmFileDetails.setDocID ( bomDocId );
                bomRevOrSchmFileDetails.setBomRevOrSchemNum ( rs.getString ( "Doc_Number" ).trim () );
                bomRevOrSchmFileDetails.setFilename ( rs.getString ( "dt_filename" ) );
                fileList.add ( bomRevOrSchmFileDetails );

            }
            bomRevOrSchmFileList.setFileList ( fileList );
        }
        catch ( Exception sqle )
        {
            System.out.println ( sqle.getMessage () );
            sqle.printStackTrace ();
            throw new ScalarException ( "Could not load Bom Documents from database" , sqle );
        }
        finally
        {
            if ( rs != null )
            {
                try
                {
                    rs.close ();
                }
                catch ( SQLException ignore )
                {
                    System.out.println ( "Error while closing the resultset" );
                }
            }
            if ( psmt != null )
            {
                try
                {
                    psmt.close ();
                }
                catch ( SQLException ignore )
                {
                    System.out.println ( "Error while closing the Statement" );
                }
            }
            if ( conn != null )
            {
                try
                {
                    conn.close ();
                }
                catch ( SQLException ignore )
                {
                    System.out.println ( "Error while closing the Connection" );
                }
            }
        }
        return bomRevOrSchmFileList;
    }

    /**
     * modified by  saravnan
     * Loads schema file deatils
     *
     */
    public static BomRevOrSchmFileList loadSchemaFileDetails ( int productId , String productType ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement psmt = null;
        ResultSet rs = null;

        String partNumber = null;
        int schmDocId = 0;
        String schmFileLocation = null;
        ArrayList SchmFileNames = new ArrayList ();
        ArrayList fileList = new ArrayList ();
        BomRevOrSchmFileList bomRevOrSchmFileList = new BomRevOrSchmFileList ();
        try
        {

            conn = DataSource.getConnection ();
            psmt = conn.prepareStatement ( "select p.product_id, p.p_marketing_part_Number, p.p_type, bk.pcb_rev_number,bk.bom_doc_id,bk.schematic_doc_id,d.doc_id,d.doc_number,d.dt_filename,d.dt_filelocation , d.Doc_Number from product p,board_kit_table bk,doc_table d where p.product_id = bk.product_id and d.doc_id = bk.schematic_doc_id and p.product_id = ?" );
            psmt.setInt ( 1 , productId );
            rs = psmt.executeQuery ();
            while ( rs.next () )
            {
                BomRevOrSchmFileDetails bomRevOrSchmFileDetails = new BomRevOrSchmFileDetails ();
                partNumber = rs.getString ( "p_marketing_part_Number" );
                productType = rs.getString ( "p_type" );
                schmDocId = rs.getInt ( "schematic_doc_id" );
                SchmFileNames.add ( rs.getString ( "dt_filename" ) );
                schmFileLocation = rs.getString ( "dt_filelocation" );
                bomRevOrSchmFileDetails.setDocID ( schmDocId );
                bomRevOrSchmFileDetails.setBomRevOrSchemNum ( rs.getString ( "Doc_Number" ).trim () );
                bomRevOrSchmFileDetails.setFilename ( rs.getString ( "dt_filename" ) );
                fileList.add ( bomRevOrSchmFileDetails );
            }

            bomRevOrSchmFileList.setFileList ( fileList );
        }
        catch ( Exception sqle )
        {
            System.out.println ( sqle.getMessage () );
            sqle.printStackTrace ();
            throw new ScalarException ( "Could not load Bom Documents from database" , sqle );
        }
        finally
        {
            if ( rs != null )
            {
                try
                {
                    rs.close ();
                }
                catch ( SQLException ignore )
                {
                    System.out.println ( "Error while closing the result set " );
                }
            }
            if ( psmt != null )
            {
                try
                {
                    psmt.close ();
                }
                catch ( SQLException ignore )
                {
                    System.out.println ( "Error while closing the Statement " );
                }
            }
            if ( conn != null )
            {
                try
                {
                    conn.close ();
                }
                catch ( SQLException ignore )
                {
                    System.out.println ( "Error while closing the Connection " );
                }
            }
        }

        return bomRevOrSchmFileList;

    }

    /**
     * Added by saravnan
     *
     * Modifies bom rev number and  schematic num files from database for the given doc_ID
     * @param dbTransaction
     * @param kitMakingForm
     * @throws ScalarException
     */
    public static void modifyBomAndSchmaFiles ( DBTransaction dbTransaction , BoardKitForm kitMakingForm ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;

        BomRevOrSchmFileList bomFileList;
        BomRevOrSchmFileList schmFileList;
        BomRevOrSchmFileDetails bomRevFileDetails;
        BomRevOrSchmFileDetails schmaFileDetails;
        ArrayList fileList;

        try
        {
            conn = dbTransaction.getConnection ();

            //Updates Bom rev files
            bomFileList = ( BomRevOrSchmFileList ) kitMakingForm.getBomRevFiles ();
            fileList = bomFileList.getFileList ();
            pstmt = conn.prepareStatement ( "update  Doc_table set Doc_Number = ?  where Doc_ID = ?" );
            for ( int i = 0 ; i < fileList.size () ; i++ )
            {
                bomRevFileDetails = ( BomRevOrSchmFileDetails ) fileList.get ( i );
                pstmt.setString ( 1 , bomRevFileDetails.getBomRevOrSchemNum () );
                pstmt.setInt ( 2 , bomRevFileDetails.getDocID () );
                pstmt.addBatch ();
            }
            pstmt.executeBatch ();
            pstmt.close ();

            // updates Schema files
            schmFileList = ( BomRevOrSchmFileList ) kitMakingForm.getSchmaFiles ();
            fileList = schmFileList.getFileList ();
            pstmt = conn.prepareStatement ( "update  Doc_table set Doc_Number = ?  where Doc_ID = ?" );
            for ( int i = 0 ; i < fileList.size () ; i++ )
            {
                schmaFileDetails = ( BomRevOrSchmFileDetails ) fileList.get ( i );
                pstmt.setString ( 1 , schmaFileDetails.getBomRevOrSchemNum () );
                pstmt.setInt ( 2 , schmaFileDetails.getDocID () );
                pstmt.addBatch ();
            }
            pstmt.executeBatch ();


        }
        catch ( Exception sqle )
        {
            System.out.println ( sqle.getMessage () );
            sqle.printStackTrace ();
            throw new ScalarException ( "Could not modify BomAndSchma Files " , sqle );
        }
        finally
        {

            if ( pstmt != null )
            {
                try
                {
                    pstmt.close ();
                }
                catch ( SQLException ignore )
                {
                   // System.out.println ( "Error while closing the Statement " );
                }
            }
            if ( conn != null )
            {
                try
                {
                    conn.close ();
                }
                catch ( SQLException ignore )
                {
                    System.out.println ( "Error while closing the connection " );
                }
            }
        }
    }


    /**
     * added by saravnan
     *
     * Deletes  seleted BomAndSchmaFiles from database
     * @param dbTransaction
     * @param kitMakingForm
     * @param productId
     * @throws ScalarException
     */
    public static void deleteBomAndSchmaFiles ( DBTransaction dbTransaction , BoardKitForm kitMakingForm , int productId ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;

        BomRevOrSchmFileList bomFileList;
        BomRevOrSchmFileList schmFileList;
        BomRevOrSchmFileDetails bomRevFileDetails;
        BomRevOrSchmFileDetails schmaFileDetails;
        ArrayList fileList;

        try
        {
            conn = dbTransaction.getConnection ();

            // Deletes BomRev files
            bomFileList = ( BomRevOrSchmFileList ) kitMakingForm.getBomRevFiles ();
            fileList = bomFileList.getFileList ();
            for ( int i = 0 ; i < fileList.size () ; i++ )
            {
                bomRevFileDetails = ( BomRevOrSchmFileDetails ) fileList.get ( i );
                if ( bomRevFileDetails.getIsFileDelete () == true )
                {
                    pstmt = conn.prepareStatement ( "delete from doc_table where doc_id = ?  " );
                    pstmt.setInt ( 1 , bomRevFileDetails.getDocID () );
                    pstmt.executeUpdate ();
                    pstmt.close ();

                    pstmt = conn.prepareStatement ( "delete from  board_kit_table where product_id = ? and bom_doc_id = ?" );
                    pstmt.setInt ( 1 , productId );
                    pstmt.setInt ( 2 , bomRevFileDetails.getDocID () );
                    pstmt.executeUpdate ();
                    pstmt.close ();
                }
            }

            //Delets SchemaFiles
            schmFileList = ( BomRevOrSchmFileList ) kitMakingForm.getSchmaFiles ();
            fileList = schmFileList.getFileList ();

            for ( int i = 0 ; i < fileList.size () ; i++ )
            {
                schmaFileDetails = ( BomRevOrSchmFileDetails ) fileList.get ( i );
                if ( schmaFileDetails.getIsFileDelete () == true )
                {
                    pstmt = conn.prepareStatement ( "delete from doc_table where doc_id = ? " );
                    pstmt.setInt ( 1 , schmaFileDetails.getDocID () );
                    pstmt.executeUpdate ();
                    pstmt.close ();

                    pstmt = conn.prepareStatement ( "delete from board_kit_table where product_id = ? and schematic_doc_id = ?" );
                    pstmt.setInt ( 1 , productId );
                    pstmt.setInt ( 2 , schmaFileDetails.getDocID () );
                    pstmt.executeUpdate ();
                    pstmt.close ();
                }
            }
        }
        catch ( Exception sqle )
        {
            System.out.println ( sqle.getMessage () );
            sqle.printStackTrace ();
            throw new ScalarException ( "Could not delete the Bom file " , sqle );
        }
        finally
        {

            if ( pstmt != null )
            {
                try
                {
                    pstmt.close ();
                }
                catch ( SQLException ignore )
                {
                    //System.out.println ( "Error while closing the Statement " );
                }
            }
            if ( conn != null )
            {
                try
                {
                    conn.close ();
                }
                catch ( SQLException ignore )
                {
                    System.out.println ( "Error while closing the connection " );
                }
            }
        }
    }

    /**
     * Added by saravnan
     *
     * updates PCBRevnumber in database
     * @param dbTransaction
     * @param kitMakingForm
     * @throws ScalarException
     */

    public static void updatePCBRevNumber ( DBTransaction dbTransaction , BoardKitForm kitMakingForm ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        try
        {
            conn = dbTransaction.getConnection ();
            pstmt = conn.prepareStatement ( "update Board_Kit_Table set PCB_Rev_Number = ? where Product_ID = ? " );
            pstmt.setString ( 1 , kitMakingForm.getPcbRevNo ().trim() );
            pstmt.setInt ( 2 , kitMakingForm.getProductId () );
            pstmt.executeUpdate ();
        }
        catch ( Exception sqle )
        {
            System.out.println ( sqle.getMessage () );
            sqle.printStackTrace ();
            throw new ScalarException ( "Could not update PCBRevNumber " , sqle );
        }
        finally
        {

            if ( pstmt != null )
            {
                try
                {
                    pstmt.close ();
                }
                catch ( SQLException ignore )
                {
                    System.out.println ( "Error while closing the statement " );
                }
            }
            if ( conn != null )
            {
                try
                {
                    conn.close ();
                }
                catch ( SQLException ignore )
                {
                    System.out.println ( "Error while closing the connection " );
                }
            }
        }

    }

    public static String getProductId ( DBTransaction dbTransaction, String productName, int parentProductId ) throws ScalarException
	{
		Connection conn  = null;
		ResultSet rs     = null;
		String productId ="";
		PreparedStatement pstmt = null;
		boolean status = false;
		try
		{
			if ( dbTransaction == null )
			{
				conn = DataSource.getConnection();
			}
			else
			{
				conn = dbTransaction.getConnection();
			}
            //SELECT * FROM Product WHERE ( Part_Number = ? or P_Marketing_Part_Number = ? ) AND P_Parent_Product_ID <> ?
			String sqlQry = SqlQueryMappings.get ("operations.process.product.selectProductIdByPartNumberAndParentId");
			pstmt = conn.prepareStatement( sqlQry );
			pstmt.setString ( 1, productName );
            pstmt.setString ( 2, productName );
			pstmt.setInt ( 3, parentProductId );
			rs = pstmt.executeQuery();
			status = true;
			while(rs.next())
			{
				productId = rs.getString(1);
			}
		}
		catch ( SQLException sql )
		{
			sql.printStackTrace();
			throw new ScalarException("In getting Base Product Id ", sql);
		}
		finally
		{
			if (rs != null) {
				try { rs.close(); } catch (SQLException ignore) {}
			}
			if (pstmt != null) {
				try { pstmt.close(); } catch (SQLException ignore) {}
			}
			if (conn != null) {
				try { conn.close(); } catch (SQLException ignore) {}
			}
        }
		return productId;
	}

    public static String checkDupInProductMapTable( DBTransaction dbTransaction, String partNumber, int processStepId, int parentProductId )throws ScalarException
	{
		Connection conn = null;
		ResultSet rs    = null;
		PreparedStatement pstmt = null;
		StringBuffer buff = new StringBuffer();
		String result = "";

		try
		{
			if ( dbTransaction == null )
			{
				conn = DataSource.getConnection();
			}
			else
			{
				conn = dbTransaction.getConnection();
			}

			if( processStepId == -1)
			{
				//pstmt = conn.prepareStatement(SqlQueryMappings.get ("operations.process.checkDupNamesFromFabMap")+"("+ buff.toString().substring( 0 , buff.length()-1) +")");
                //SELECT Part_Number FROM Product_Map_Table WHERE  Part_Number = ?
                pstmt = conn.prepareStatement ( SqlQueryMappings.get ( "operations.process.checkDupPartNumbersInProductMapTableForNew" ) );
				pstmt.setString ( 1 , partNumber );
			}
			else
			{
				//pstmt = conn.prepareStatement(SqlQueryMappings.get ("operations.process.checkDupNamesFromFabMapWithProcessStepID")+"("+ buff.toString().substring( 0 , buff.length()-1) +")");
                //SELECT PMT.Part_Number FROM Product INNER JOIN Product_Map_Table PMT ON Product.ProcessStep_ID = PMT.ProcessStep_ID WHERE  Product.P_Parent_Product_ID <> ?  AND PMT.Part_Number = ?
                pstmt = conn.prepareStatement ( SqlQueryMappings.get ( "operations.process.checkDupPartNumbersInProductMapTable" ) );
				pstmt.setInt ( 1 , parentProductId );
				pstmt.setString ( 2 , partNumber );
			}

			rs =  pstmt.executeQuery();

			while( rs.next() )
			{
				result = rs.getString ( "Part_Number" );
			}

		}
		catch (Exception sqle)
		{
			sqle.printStackTrace();
			throw new ScalarException ("Could not get check Duplicate Fab Part Numbers from database", sqle);
		}
		finally
		{
		   if (rs != null) {
			   try { rs.close(); } catch (SQLException ignore) {}
		   }
		   if (pstmt != null) {
			   try { pstmt.close(); } catch (SQLException ignore) {}
		   }
		   if (conn != null) {
			   try { conn.close(); } catch (SQLException ignore) {}
		   }
		}
		return result;
	}

}






